package com.sfh.service.util;

import java.util.ArrayList;
import java.util.List;

public class SQLHelp {

	class Where {
		Enum<?> key;
		Object value;
		String option;
		int type ;
	}

	Enum<?> table;
	List<Where> condition = new ArrayList<Where>(2);
	Enum<?>[] soreKey;
	Enum<?>[] queryKeys;
	Enum<?>[] groupby;

	Enum<?> sum[];
	String having;
	int limit;

	public SQLHelp(Enum<?> table) {
		this.table = table;
	}
	/***
	 * 查询特定键的特定值
	 * 
	 * @param key
	 * @param value
	 */
	public SQLHelp addWhereEqualTo(Enum<?> key, int value) {
		Where where = new Where();
		where.key = key;
		where.value = value;
		where.option = "%s=%s";
		this.condition.add(where);
		return this;
	}
	/***
	 * 查询特定键的特定值
	 * 
	 * @param key
	 * @param value
	 */
	public SQLHelp addWhereEqualTo(Enum<?> key, String value) {
		Where where = new Where();
		where.key = key;
		where.value = value;
		where.option = "%s='%s'";
		this.condition.add(where);
		return this;
	}
	/***
	 * 查询特定键的特定值
	 * 
	 * @param key
	 * @param value
	 */
	public SQLHelp addWhereEqualTo(Enum<?> key, Object value) {
		Where where = new Where();
		where.key = key;
		where.value = value;
		where.option = "%s=%s";
		this.condition.add(where);
		return this;
	}
	/***
	 * 查询特定键的特定值
	 * 
	 * @param key
	 * @param value
	 */
	public SQLHelp addWhereEqualTo(Enum<?> key, long value) {
		Where where = new Where();
		where.key = key;
		where.value = value;
		where.option = "%s=%s";
		this.condition.add(where);
		return this;
	}
	
	/***
	 * 过滤掉特定键的值
	 * 
	 * @param key
	 * @param value
	 */
	public SQLHelp addWhereNotEqualTo(Enum<?> key, Object value) {
		Where where = new Where();
		where.key = key;
		where.value = value;
		where.option = "%s!=%s";
		this.condition.add(where);
		return this;
	}

	/***
	 * 条件:大于
	 */
	public SQLHelp addWhereGreaterThan(Enum<?> key, String value) {
		Where where = new Where();
		where.key = key;
		where.value = value;
		where.option = "%s>%s";
		this.condition.add(where);
		return this;
	}

	/***
	 * 条件:大于等于
	 */
	public SQLHelp addWhereGreaterThanOrEqualTo(Enum<?> key, Object value) {
		Where where = new Where();
		where.key = key;
		where.value = value;
		where.option = "%s>=%s";
		this.condition.add(where);
		return this;
	}

	/**
	 * 条件:小于
	 * 
	 * @param key
	 * @param value
	 */
	public SQLHelp addWhereLessThan(Enum<?> key, Object value) {
		Where where = new Where();
		where.key = key;
		where.value = value;
		where.option = "%s<%s";
		this.condition.add(where);
		return this;
	}

	/**
	 * 条件:小于等于
	 * 
	 * @param key
	 * @param value
	 */
	public SQLHelp addWhereLessThanOrEqualTo(Enum<?> key, Object value) {
		Where where = new Where();
		where.key = key;
		where.value = value;
		where.option = "%s<=%s";
		this.condition.add(where);
		return this;
	}

	/**
	 * 模糊查询
	 * 
	 * @param key
	 * @param value
	 */
	public SQLHelp addWhereMatches(Object value,Enum<?>... key) {
		Where where = new Where();
		where.type = 1;
		if (key.length > 1) {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.append(" concat(");
			final int size = key.length;
			for (int i=0; i<size; i++ ) {
				stringBuilder.append(key[i]);
				if (i < size-1) {
					stringBuilder.append(",");
				}
			}
			stringBuilder.append(")");
			where.option =stringBuilder + " LIKE '%"+value+"%'";
		}else {
			where.option = key+" LIKE '%"+value+"%'";
		}
		this.condition.add(where);
		return this;
	}

	/***
	 * 设置返回的记录数量
	 * 
	 * @param size
	 */
	public SQLHelp setLimit(int size) {
		this.limit = size;
		return this;
	}

	String sore;

	/***
	 * 降序排序[多个字段排序时，先按第一个字段进行排序，再按第二个字段进行排序]
	 */
	public SQLHelp sore(String sore, Enum<?>... key) {
		this.sore = sore;
		this.soreKey = key;
		return this;
	}

	/***
	 * 查询指定列
	 * 
	 * @param key
	 */
	public SQLHelp addQueryKeys(Enum<?>... key) {
		this.queryKeys = key;
		return this;
	}

	/***
	 * 查询结果进行分组
	 * 
	 * @param key
	 */
	public SQLHelp groupby(Enum<?>... key) {
		this.groupby = key;
		return this;
	}

	/***
	 * 总和
	 */
	public SQLHelp sum(Enum<?>... key) {
		sum = key;
		return this;
	}

	/***
	 * 对分组计算后的结果再进行过滤
	 * 
	 * @param value
	 */
	public SQLHelp having(String value) {
		this.having = value;
		return this;
	}

	/***
	 * 完整sql
	 * 
	 * @return
	 */
	public String build() {

		StringBuilder builder = new StringBuilder();

		// 指定列
		builder.append(getSelect());
		builder.append(String.format(" From %s", table));
		// 条件
		builder.append(getWhere());
		// 分组
		builder.append(getGroupby());
		// 排序
		builder.append(getSore());

		if (limit > 0) {
			builder.append(String.format("limit %d", limit));
		}
		System.out.println("SQL:" + builder.toString());
		return builder.toString();
	}

	class Update {
		Enum<?> key;
		Object value;

		Update(Enum<?> key, Object value) {
			this.key = key;
			this.value = value;
		}
	}

	List<Update> updates = new ArrayList<>(2);

	public void updateKeys(Enum<?> key, Object value) {
		updates.add(new Update(key, value));
	}

	/***
	 * 更新sql
	 * 
	 * @return
	 */
	public String buildUpdaete() {

		// UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
		String UPDATE = "UPDATE %s SET ";

		StringBuilder builder = new StringBuilder();
		builder.append(String.format(UPDATE, table));

		// 指定列
		builder.append(getUpdateSelect());
	
		// 条件
		builder.append(getWhere());
	
		System.out.println("SQL:" + builder.toString());
		return builder.toString();
	}

	private String getUpdateSelect() {

		String select = "%s=%s";

		final int size = this.updates.size();

		StringBuilder entry = new StringBuilder();
		for (int i = 0; i < size; i++) {

			Update update = this.updates.get(i);

			entry.append(String.format(select, update.key, update.value));
			if (i < size - 1) {
				entry.append(",");
			}
		}
		return entry.toString();

	}

	/***
	 * From之后sql（包含From）
	 * 
	 * @return
	 */
	public String buildFrom() {

		StringBuilder builder = new StringBuilder();

		builder.append(String.format(" From %s", table));
		// 条件
		builder.append(getWhere());
		// 分组
		builder.append(getGroupby());
		// 排序
		builder.append(getSore());

		if (limit > 0) {
			builder.append(String.format("limit %d", limit));
		}
		System.out.println("sql:"+builder);
		return builder.toString();
	}

	public String getSelect() {
		String select = "SELECT %s ";
		// 指定列
		if (this.queryKeys != null) {

			final int size = this.queryKeys.length;

			StringBuilder entry = new StringBuilder();
			for (int i = 0; i < size; i++) {
				entry.append(this.queryKeys[i]);
				if (i < size - 1) {
					entry.append(",");
				}
			}
			return String.format(select, entry);
		} else {
			return String.format(select, "*");
		}
	}

	private String getWhere() {
		// 条件
		String sql = " WHERE %s ";

		if (!this.condition.isEmpty()) {
			StringBuilder builder = new StringBuilder();
			final int size = this.condition.size();
			for (int i = 0; i < size; i++) {
				Where where = this.condition.get(i);
				if (where.type == 1) {
					builder.append(where.option);	
				}else {
					builder.append(String.format(where.option, where.key, where.value));	
				}
				if (i < size - 1) {
					builder.append(" AND ");
				}
			}
			return String.format(sql, builder);

		} else {
			return String.format(sql, "1=1");
		}

	}

	private String getGroupby() {
		if (this.groupby != null) {
			String sql = " GROUP BY %s";
			final int size = this.groupby.length;

			StringBuilder entry = new StringBuilder();
			for (int i = 0; i < size; i++) {
				entry.append(this.groupby[i]);
				if (i < size - 1) {
					entry.append(",");
				}
			}
			return String.format(sql, entry);
		}
		return "";
	}

	private String getSore() {
		if (this.soreKey != null) {
			String sql = " ORDER BY  %s %s";
			final int size = this.soreKey.length;

			StringBuilder entry = new StringBuilder();
			for (int i = 0; i < size; i++) {
				entry.append(this.soreKey[i]);
				if (i < size - 1) {
					entry.append(",");
				}
			}
			return String.format(sql, entry, sore);
		}
		return "";
	}
}
